#!/usr/bin/env python
# -*- coding: utf-8 -*-

import dianputools


# the num of booking product
def query_num_of_boking_product(s, e, p=None):
    query = 'select count(1) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s"' % (s, e)
    if p is not None:
        query += ' and op.productid = %s' % (p)

    return query


# the num of booking order
def query_num_of_boking_order(s, e, p=None):
    query = 'select count(distinct o.orderid) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s"' % (s, e)
    if p is not None:
        query += ' and op.productid = %s' % (p)

    return query


# the num of booking order fee
def query_num_of_boking_order_fee(s, e, p=None):
    query = 'select sum(price) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s"' % (s, e)
    if p is not None:
        query += ' and op.productid = %s' % (p)

    return query


# # the num of order
# query = 'select count(1) from t_order where createdate>"%s" and createdate<"%s"' % (s, e)

# the num of payed order
def query_num_of_paid_order(s, e, p=None):
    if p is None:
        query = 'select count(1) from t_order where state in(3,4,5,8) and createdate > "%s" and createdate < "%s"' % (s, e)
    else:
        query = 'select count(distinct o.orderid) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where state in(3,4,5,8) and o.createdate > "%s" and o.createdate < "%s and op.productid = %s"' % (s, e, p)

    return query


# the num of payed product
def query_num_of_paid_product(s, e, p):
    query = 'select count(1) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where state in(3,4,5,8) and o.createdate > "%s" and o.createdate < "%s and op.productid = %s"' % (s, e, p)

    return query


# the num of user payed
def query_num_of_user_paid(s, e, p=None):
    if p is None:
        query = 'select count(distinct buyerid) from t_order where state in(3,4,5,8) and createdate > "%s" and createdate < "%s"' % (s, e)
    else:
        query = 'select count(distinct op.buyerid) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where state in(3,4,5,8) and o.createdate > "%s" and o.createdate < "%s and op.productid = %s"' % (s, e, p)

    return query




# the num of new user payed
def query_num_of_new_user_paid(s, e, p=None):
    if p is None:
        query = 'select count(1) from (select buyerid from t_order where state in(3,4,5,8) group by buyerid having min(date(createdate))="%s") tmp' % (s)
    else:
        query = 'select count(1) from (select o.buyerid from t_order o left outer join t_orderproduct op on o.orderid = op.orderid  where o.state in(3,4,5,8) and op.productid = "%s" group by o.buyerid having min(date(o.createdate))="%s") tmp' % (p, s)

    return query


# total fee
def query_num_of_order_total_fee(s, e, p=None):
    if p is None:
        query = 'select sum(ordertotal) from t_order where state in(3,4,5,8) and createdate > "%s" and createdate < "%s"' % (s, e)
    else:
        query = 'select sum(ordertotal) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where state in(3,4,5,8) and o.createdate > "%s" and o.createdate < "%s" and op.productid = "%s"' % (s, e, p)

    return query


# # commission
# query = 'select count(1) from (select buyerid from t_order where paydate is not null group by buyerid having min(date(paydate))="%s") tmp' % (s)






# # TODO
# def query_num_of_ticket_consumed(s, e, p=None):
#     query = 'select count(1) from t_orderticket where tickettype=0 and lifestate=0 and state=0 and consumedate > "%s" and consumedate < "%s"' % (s, e)
#     return query
#
# def query_num_of_order_consumed(s, e, p=None):
#     query = 'select count(1) from t_ticketlog where cashdate > "%s" and cashdate < "%s"' % (s, e)
#     return query
#
# def query_num_of_fee_consumed(s, e, p=None):
#     query = 'select sum(op.price) from t_orderproduct op left outer join t_orderticket ot on op.id=ot.orderproductid left outer join t_ticketlog otl on ot.id=otl.ticketid where otl.cashdate > "%s" and otl.cashdate < "%s"' % (s, e)
#     return query




# # commission
# query = 'select count(1) from t_orderticket where tickettype=0 and lifestate=0 and state=0 and date(consumedate)="%s"' % (s)

# order back before ticket checking
def query_order_back_before_ticket_checking(s, e, p=None):
    query = 'select count(distinct o.orderid) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=0' % (s, e)
    if p is not None:
        query += ' and op.productid = %s' % (p)

    return query


# product back before ticket checking
def query_product_back_before_ticket_checking(s, e, p):
    query = 'select count(1) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=0 and op.productid = %s' % (s, e, p)

    return query



# user back before ticket checking
def query_user_back_before_ticket_checking(s, e, p=None):
    query = 'select count(distinct op.buyerid) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=0' % (s, e)
    if p is not None:
        query += ' and op.productid = %s' % (p)

    return query


# fee back before ticket checking
def query_fee_back_before_ticket_checking(s, e, p=None):
    query = 'select sum(op.price)  from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=0' % (s, e)
    if p is not None:
        query += ' and op.productid = %s' % (p)

    return query


# # order back after ticket checking
# query = 'select count(1) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=1' % (s, e)


# user back after ticket checking
def query_user_back_after_ticket_checking(s, e, p=None):
    query = 'select count(distinct op.buyerid) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=1' % (s, e)
    if p is not None:
        query += ' and op.productid = %s' % (p)

    return query


# # fee back after ticket checking
# query = 'select sum(op.price)  from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=1' % (s, e)


def query_comment(s, e, p=None):
    query = 'select type from comment where date >= "%s" and date < "%s"' % (s, e)
    if p is not None:
        query += ' and objectid = %s' % (p)

    return query

if __name__ == '__main__':
    DEBUG = True
    y, m, d = 2012, 4, 16
    dtstart, dtend = dianputools.str_day_start_and_end(y, m, d)
    p = '6433502175745'

    print query_num_of_boking_product(dtstart, dtend, p)
    print query_num_of_boking_order(dtstart, dtend, p)
    print query_num_of_boking_order_fee(dtstart, dtend, p)
    print query_num_of_paid_order(dtstart, dtend, p)
    print query_num_of_paid_product(dtstart, dtend, p)
    print query_num_of_order_total_fee(dtstart, dtend, p)
    print query_num_of_user_paid(dtstart, dtend, p)
    print query_order_back_before_ticket_checking(dtstart, dtend, p)
    print query_product_back_before_ticket_checking(dtstart, dtend, p)
    print query_fee_back_before_ticket_checking(dtstart, dtend, p)
    print query_user_back_before_ticket_checking(dtstart, dtend, p)
    print query_user_back_after_ticket_checking(dtstart, dtend, p)
    print query_comment(dtstart, dtend, p)
